package com.Utils;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {
    /**
     * 获取工作薄
     * @param in  excel文件
     * @return  工作薄对象
     */
   public static Workbook getWorkbook(FileInputStream in){
        Workbook workbook =null;
        try {
            workbook = WorkbookFactory.create(in);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return workbook;
    }
    //获取单元格各类型值，返回字符串类型
    public static String getCellValue(Cell cell) {
        //判断是否为null或空串
        if (cell==null){
            return "";
        }
        String cellValue = "";
        int cellType=cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellValue = sdf.format(date);
                    }else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }  else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue()+"";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "";
                break;
        }
        //返回单元格值并去除首尾空格和去除空格
        return cellValue.trim().replace(" ","");
    }

    /**
     * 判断读取的行内容是否为空
     * @param array 整行的excel数据
     * @return
     */
    public static boolean isAllElemEmpty(Object[] array){
       if (ArrayUtils.isEmpty(array)){
           //如果数组是空的 直接返回true
           return true;
       }
       boolean flag=true;
        for (Object o : array) {
            if (StringUtils.isNotEmpty(ObjectUtils.toString(o))){
                flag=false;
                break;
            }
        }
        return flag;
    }
    public static Map<String, List<Object[]>>readTomap(FileInputStream in){
       //准备好要返回的数据类型
        Map<String, List<Object[]>> map = new HashMap<String, List<Object[]>>();
        //获取工作薄
        Workbook workbook = getWorkbook(in);
        //获取工作薄的页数
        int ofSheets = workbook.getNumberOfSheets();
        //遍历每一页
        for (int i = 0; i < ofSheets; i++) {
            //准备好存每一页的数据类型
            List<Object[]> list = new ArrayList<Object[]>();
            Sheet sheet = workbook.getSheetAt(i);
            //获取有内容的开始行
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();
            //遍历每一行
            for (int j = firstRowNum;j<=lastRowNum;j++){
                Row row = sheet.getRow(j);
                if (row==null){
                    continue;
                }
                //获取有内容的开始单元格和结束单元格
                short firstCellNum = row.getFirstCellNum();
                short lastCellNum = row.getLastCellNum();
                int len = lastCellNum-firstCellNum;
                Object[] obj = new Object[len];
                //遍历每一个单元格
                for (int k = 0;k<len;k++){
                    Cell cell = row.getCell(k);
                    obj[k] = getCellValue(cell);
                }
                boolean b = isAllElemEmpty(obj);
                if (!b) {
                    list.add(obj);
                }
            }
            if (CollectionUtils.isNotEmpty(list)){
                map.put(sheet.getSheetName().trim(),list);
            }

        }
        return map;
    }
}
